Tuesday, October 30, 2012

a contour map for open office calc

I switched from using Excel to Open Office a few years ago. I like the OO package - it works very well and it's free. There is only one problem with it, which has been bugging me for years. There is no way to make a 3D contour map in Calc. There are lots of 2D chart visualizations, and 3D versions of those 2D charts, but no way to display a 3D surface from a table of data. This is something easily implemented in Excel, and a glaring deficiency in Open Office Calc.

However, I still need that functionality. It occurred to me that a 2D map can use color as a representation of a third dimension. The greater the number of colors available, the finer the resolution on that third dimension. A table of data could be searched for the minimum and maximum values, and the difference divided by the number of colors gives a range of values for each color. A second table is then made from the first table, with a color number in place of the original data. Finally, a third table is made from the second table, with a colored square in place of the color number.

This is made possible using the STYLE() and CHOOSE() functions. First, set aside a section of worksheet (or start a new sheet) and change the background colors on a number of cells. I chose to use 32 colors, and arrayed them in a spectrum:

The names of each color is listed; on your worksheet you may want to change the text color to match the background color in each of these 32 cells. The spectrum above isn't perfect, and you might want to switch the colors around a bit or use different colors - just make something that is pleasing to your eye.

Once you have this spectrum, click on a colored cell and then press the F11 key. This will bring up the Styles popup. Create a new Style for each of the cells, and give it a short name (the left column shows the names I chose, C00 through C31). Repeat this for each of the colored cells so you have 32 Styles.

When you have a table you want to display as a color contour map, you need to convert your original data to color numbers. I have 32 colors in my spectrum, so the minimum value on my table gets the color number 0, the maximum value gets color number 31, and the others are in between. I used a linear formula to convert my table values to color numbers, but there is no reason that couldn't be logarithmic or some other conversion formula. It's up to you.

Once the intermediate table of color numbers is generated, the third table of the actual colors is easy. Let's suppose that the top left corner of my color number table is cell BA3, and that I'm putting the top left corner of my contour map at B3. If I just wanted to put color number 0 in that cell, the formula for B3 would be:
= STYLE("C00")
However, I want to choose from one of 32 possible colors. The CHOOSE() function can only choose from up to 30 possibilities, so I needed to trick Calc a little bit. Here's the revised formula for cell B3:
= IF(BA3<16; STYLE(CHOOSE(BA3+1;"C00";"C01";"C02";"C03";"C04";"C05";"C06";"C07";"C08";"C09";"C10";"C11";"C12";"C13";"C14";"C15")); STYLE(CHOOSE(BA3-15;"C16";"C17";"C18";"C19";"C20";"C21";"C22";"C23";"C24";"C25";"C26";"C27";"C28";"C29";"C30";"C31")))

Next, copy and paste this cell across the remainder of the row of the table, then copy and paste that row all the way down the table. Presto! You now have a color contour map of your data in ooCalc.